using System;
using System.IO;
using System.Collections;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(
       DataAccess = DataAccessKind.None,
       FillRowMethodName = "FillRowDirectoryList",
       IsDeterministic = true,
       Name = "DirectoryList",
       TableDefinition = @"Name nvarchar(4000), Directory bit, Size bigint, DateCreated datetime, DateModified datetime, Extension nvarchar(4000)")]

    public static IEnumerable DirectoryList(SqlString path, SqlString filter)
    {
        Int16 errSeverity = 16;

        try
        {
            DirectoryInfo di = new DirectoryInfo(path.Value);
            if (filter.IsNull || filter.Value == string.Empty)
                return di.GetFileSystemInfos();
            else
                return di.GetFileSystemInfos(filter.Value);
        }
        catch (Exception ex)
        {
            // Connect to server in event of error
            SqlConnection conn = new SqlConnection("context connection=true");
            conn.Open();

            // Generate error response to SQL Server
            string msg = string.Format("RAISERROR('{0}',{1},1)", ex.Message.Replace('\'', ' '), errSeverity);
            SqlCommand cmd = new SqlCommand(msg, conn);

            try
            {
                // Execute error on SQL Server
                SqlPipe sqlpipe = SqlContext.Pipe;
                sqlpipe.ExecuteAndSend(cmd);
                Exception newEx = new Exception(msg);
                throw newEx;
            }
            catch { return null; } // Prevent error duplication
        }
    }

    private static void FillRowDirectoryList(object obj,
        out SqlString altname,
        out SqlBoolean directoryflag,
        out SqlInt64 size,
        out SqlDateTime datetimecreate,
        out SqlDateTime datetimemodified,
        out SqlString extension)
    {
        try
        {
            if (obj is FileInfo)
            {
                FileInfo fsi = (FileInfo)obj;
                altname = fsi.Name;
                directoryflag = SqlBoolean.False;
                size = fsi.Length;
                datetimecreate = fsi.CreationTime;
                datetimemodified = fsi.LastWriteTime;
                extension = fsi.Extension.ToString();
            }
            else
            {
                FileSystemInfo fsi = (FileSystemInfo)obj;
                altname = fsi.Name;
                directoryflag = SqlBoolean.True;
                size = SqlInt64.Null;
                datetimecreate = fsi.CreationTime;
                datetimemodified = fsi.LastWriteTime;
                extension = fsi.Extension.ToString();
            }
        }
        catch (Exception e)
        {
            throw e;
        }
    }
};

